﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Collections;
using System.Text.RegularExpressions;

namespace ThiTracNghiem
{
    public partial class FormQLDSSinhVien : Form
    {
        public String currentMalop = "";
        public ArrayList listMaLop = new ArrayList();
        Stack<SinhVien> undoStack = new Stack<SinhVien>();

        public static bool ValidateInput(KeyPressEventArgs e)
        {
            bool blDiscardUserInput = true;

            if (Char.IsLetter(e.KeyChar))
                return false;

            // Backspace or Space
            if (e.KeyChar == 8 || e.KeyChar == (char)Keys.Space)
                blDiscardUserInput = false;

            return blDiscardUserInput;
        }

        public static bool ValidateInputMa(KeyPressEventArgs e)
        {
            bool blDiscardUserInput = true;

            if (Char.IsLetter(e.KeyChar))
                return false;

            if (Char.IsNumber(e.KeyChar))
                return false;

            // Backspace or Space
            if (e.KeyChar == 8)
                blDiscardUserInput = false;

            return blDiscardUserInput;
        }

        public bool isExistingInDGV(String maSV)
        {
            foreach (DataGridViewRow row in dgvSV.Rows)
            {
                if (row.Cells[0].Value.ToString().ToUpper().Equals(maSV.ToUpper()))
                    return true;
            }
            return false;
        }

        public FormQLDSSinhVien()
        {
            InitializeComponent();
        }

        private void FormQLDSSinhVien_Load(object sender, EventArgs e)
        {
            using (SqlConnection con = new SqlConnection(Program.ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand("SP_GETALL_LOP", con))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    try
                    {
                        con.Open();
                        SqlDataReader reader = cmd.ExecuteReader();
                        while (reader.Read())
                        {
                            listMaLop.Add(reader[0].ToString().Trim());
                            cbDSLop.Items.Add(reader[1].ToString().Trim());
                        }
                        reader.Close();
                    }
                    catch (System.Exception ex)
                    {
                        MessageBox.Show("Không load được danh sách lớp. \n Lý do: " + ex.Message, "Lỗi", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    }
                }
            }
        }

        private void cbDSLop_SelectedIndexChanged(object sender, EventArgs e)
        {

            currentMalop = (String)listMaLop[cbDSLop.SelectedIndex];
            txtMaLop.Text = currentMalop;
            btnThem.Enabled = true;
            btnSua.Enabled = true;
            btnXoa.Enabled = true;
            btnTim.Enabled = true;

            dgvSV.Rows.Clear();
            using (SqlConnection con = new SqlConnection(Program.ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand("SP_GETALL_SV_THEO_LOP", con))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@MALOP", SqlDbType.Char).Value = (String)listMaLop[cbDSLop.SelectedIndex];
                    try
                    {
                        con.Open();
                        SqlDataReader reader = cmd.ExecuteReader();
                        while (reader.Read())
                        {
                            DataGridViewRow row = new DataGridViewRow();
                            row.CreateCells(dgvSV);
                            row.Cells[0].Value = reader[0].ToString().Trim();
                            row.Cells[1].Value = reader[1].ToString().Trim();
                            row.Cells[2].Value = reader[2].ToString().Trim();
                            DateTime date = DateTime.Parse(reader[3].ToString().Trim());
                            row.Cells[3].Value = date.Day.ToString("00") + "/" + date.Month.ToString("00") + "/" + date.Year;
                            row.Cells[4].Value = reader[4].ToString().Trim();
                            dgvSV.Rows.Add(row);
                        }
                        reader.Close();
                    }
                    catch (System.Exception ex)
                    {
                        MessageBox.Show("Không load được danh sách lớp. \n Lý do: " + ex.Message, "Lỗi", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    }
                }
            }
        }

        private void btnRefreshDSLop_Click(object sender, EventArgs e)
        {
            cbDSLop.Items.Clear();
            listMaLop.Clear();
            using (SqlConnection con = new SqlConnection(Program.ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand("SP_GETALL_LOP", con))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    try
                    {
                        con.Open();
                        SqlDataReader reader = cmd.ExecuteReader();
                        while (reader.Read())
                        {
                            listMaLop.Add(reader[0].ToString().Trim());
                            cbDSLop.Items.Add(reader[1].ToString().Trim());
                        }
                        reader.Close();
                    }
                    catch (System.Exception ex)
                    {
                        MessageBox.Show("Không load được danh sách lớp. \n Lý do: " + ex.Message, "Lỗi", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    }
                }
            }
            if (cbDSLop.Items.Count > 0)
            {
                cbDSLop.SelectedIndex = 0;
            }
        }

        private void dgvSV_SelectionChanged(object sender, EventArgs e)
        {
            if (dgvSV.SelectedRows.Count > 0)
            {
                DataGridViewRow row = dgvSV.SelectedRows[0];
                txtMaSV.Text = row.Cells[0].Value.ToString();
                txtHo.Text = row.Cells[1].Value.ToString();
                txtTen.Text = row.Cells[2].Value.ToString();
                txtDiaChi.Text = row.Cells[4].Value.ToString();
                String sdate = row.Cells[3].Value.ToString();
                dtpNgaySinh.Value = new DateTime(int.Parse(sdate.Substring(6,4)), int.Parse(sdate.Substring(3,2)), int.Parse(sdate.Substring(0,2)));
            }
            else
            {            
                txtMaSV.Text = "";
                txtHo.Text = "";
                txtTen.Text = "";
                txtDiaChi.Text = "";
                dtpNgaySinh.Value = dtpNgaySinh.MinDate;
            }
        }

        private void btnThem_Click(object sender, EventArgs e)
        {
            txtMaSV.ReadOnly = false;
            txtHo.ReadOnly = false;
            txtTen.ReadOnly = false;
            txtDiaChi.ReadOnly = false;
            dtpNgaySinh.Enabled = true;
            txtMaSV.Text = "";
            txtHo.Text = "";
            txtTen.Text = "";
            txtDiaChi.Text = "";
            dtpNgaySinh.Value = dtpNgaySinh.MinDate;
            

            pnButtonThemHuy.Visible = true;
            btnOK.Text = "Thêm";

            btnThem.Enabled = false;
            btnSua.Enabled = false;
            btnXoa.Enabled = false;
            btnTim.Enabled = false;
            dgvSV.Enabled = false;
        }

        String backupMaSV = "";
        String backupHo = "";
        String backupTen = "";
        String backupDiaChi = "";
        DateTime backupNgaySinh;
        private void btnSua_Click(object sender, EventArgs e)
        {
            if (dgvSV.SelectedRows.Count > 0)
            {
                txtMaSV.ReadOnly = false;
                txtHo.ReadOnly = false;
                txtTen.ReadOnly = false;
                txtDiaChi.ReadOnly = false;
                dtpNgaySinh.Enabled = true;
                dgvSV.Enabled = false;

                pnButtonThemHuy.Visible = true;
                btnOK.Text = "Xong";

                btnThem.Enabled = false;
                btnSua.Enabled = false;
                btnXoa.Enabled = false;
                btnTim.Enabled = false;

                backupMaSV = txtMaSV.Text;
                backupHo = txtHo.Text;
                backupTen = txtTen.Text;
                backupDiaChi = txtDiaChi.Text;
                backupNgaySinh = dtpNgaySinh.Value;
            }
        }

        private void btnOK_Click(object sender, EventArgs e)
        {
            txtHo.Text = Regex.Replace(txtHo.Text, @"\s+", " ").Trim();
            txtTen.Text = Regex.Replace(txtTen.Text, @"\s+", " ").Trim();
            txtDiaChi.Text = Regex.Replace(txtDiaChi.Text, @"\s+", " ").Trim();
            txtMaSV.Text = Regex.Replace(txtMaSV.Text, @"\s+", " ").Trim();

            if (txtMaSV.Text.Equals("") || txtHo.Text.Equals("") || txtTen.Text.Equals(""))
            {
                MessageBox.Show("Bạn phải nhập đầy đủ các trường thông tin.", "Cảnh báo", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }
            
            if (btnOK.Text.Equals("Thêm"))
            {
                if (isExistingInDGV(txtMaSV.Text))
                {
                    MessageBox.Show("Mã số Sinh viên bạn vừa nhập đã tồn tại.", "Cảnh báo", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                    return;
                }

                int isExisting = 0;
                using (SqlConnection con = new SqlConnection(Program.ConnectionString))
                {
                    using (SqlCommand cmd = new SqlCommand())
                    {
                        cmd.Connection = con;
                        cmd.CommandText = "select dbo.FN_ISEXISTING_MASV('" + txtMaSV.Text + "','" + currentMalop +"')";
                        cmd.CommandType = CommandType.Text;
                        try
                        {
                            con.Open();
                            isExisting = (int)cmd.ExecuteScalar();
                        }
                        catch (System.Exception ex)
                        {
                            MessageBox.Show(ex.Message, "Lỗi", MessageBoxButtons.OK, MessageBoxIcon.Error);
                            return;
                        }
                    }
                }
                if (isExisting == 1)
                {
                    MessageBox.Show("Mã số Sinh viên bạn vừa nhập đã tồn tại.", "Cảnh báo", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                    return;
                }

                DataGridViewRow row = new DataGridViewRow();
                row.CreateCells(dgvSV);
                row.Cells[0].Value = txtMaSV.Text;
                row.Cells[1].Value = txtHo.Text;
                row.Cells[2].Value = txtTen.Text;
                DateTime date = dtpNgaySinh.Value;
                row.Cells[3].Value = date.Day.ToString("00") + "/" + date.Month.ToString("00") + "/" + date.Year;
                row.Cells[4].Value = txtDiaChi.Text;
                dgvSV.Rows.Add(row);
                SinhVien sv = new SinhVien(1, txtMaSV.Text, txtHo.Text, txtTen.Text, dtpNgaySinh.Value, txtDiaChi.Text, txtMaLop.Text);
                undoStack.Push(sv);
                btnUndo.Enabled = true;

                pnButtonThemHuy.Visible = false;
                dgvSV.Rows[dgvSV.RowCount - 1].Selected = true;
                dgvSV.Sort(dgvSV.Columns[0], ListSortDirection.Ascending);

                txtMaSV.ReadOnly = true;
                txtHo.ReadOnly = true;
                txtTen.ReadOnly = true;
                txtDiaChi.ReadOnly = true;
                dtpNgaySinh.Enabled = false;

                btnThem.Enabled = true;
                btnSua.Enabled = true;
                btnXoa.Enabled = true;
                btnTim.Enabled = true;
                dgvSV.Enabled = true;
            }
            else
            {
                if (backupMaSV.ToUpper().Equals(txtMaSV.Text.ToUpper()) && backupHo.ToUpper().Equals(txtHo.Text.ToUpper()) && backupTen.ToUpper().Equals(txtTen.Text.ToUpper()) && backupNgaySinh.Equals(dtpNgaySinh.Value) && backupDiaChi.ToUpper().Equals(txtDiaChi.Text.ToUpper()))
                {

                }
                else
                {
                    if (!backupMaSV.ToUpper().Equals(txtMaSV.Text.ToUpper()))
                    {
                        if (isExistingInDGV(txtMaSV.Text))
                        {
                            MessageBox.Show("Mã số Sinh viên bạn vừa nhập đã tồn tại.", "Cảnh báo", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                            return;
                        }

                        int isExisting = 0;
                        using (SqlConnection con = new SqlConnection(Program.ConnectionString))
                        {
                            using (SqlCommand cmd = new SqlCommand())
                            {
                                cmd.Connection = con;
                                cmd.CommandText = "select dbo.FN_ISEXISTING_MASV('" + txtMaSV.Text + "','" + currentMalop + "')";
                                cmd.CommandType = CommandType.Text;
                                try
                                {
                                    con.Open();
                                    isExisting = (int)cmd.ExecuteScalar();
                                }
                                catch (System.Exception ex)
                                {
                                    MessageBox.Show(ex.Message, "Lỗi", MessageBoxButtons.OK, MessageBoxIcon.Error);
                                    return;
                                }
                            }
                        }
                        if (isExisting == 1)
                        {
                            MessageBox.Show("Mã số Sinh viên bạn vừa nhập đã tồn tại.", "Cảnh báo", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                            return;
                        }
                    }
                    SinhVien sv = new SinhVien(2, txtMaSV.Text, txtHo.Text, txtTen.Text, dtpNgaySinh.Value, txtDiaChi.Text, txtMaLop.Text);
                    sv.MaSVOld = backupMaSV;
                    sv.HoOld = backupHo;
                    sv.TenOld = backupTen;
                    sv.DiaChiOld = backupDiaChi;
                    sv.NgaySinhOld = backupNgaySinh;

                    undoStack.Push(sv);
                    btnUndo.Enabled = true;
                    DataGridViewRow row = dgvSV.SelectedRows[0];
                    row.Cells[0].Value = txtMaSV.Text;
                    row.Cells[1].Value = txtHo.Text;
                    row.Cells[2].Value = txtTen.Text;
                    DateTime date = dtpNgaySinh.Value;
                    row.Cells[3].Value = date.Day.ToString("00") + "/" + date.Month.ToString("00") + "/" + date.Year;
                    row.Cells[4].Value = txtDiaChi.Text;
                }

                pnButtonThemHuy.Visible = false;
                dgvSV.Sort(dgvSV.Columns[0], ListSortDirection.Ascending);

                txtMaSV.ReadOnly = true;
                txtHo.ReadOnly = true;
                txtTen.ReadOnly = true;
                txtDiaChi.ReadOnly = true;
                dtpNgaySinh.Enabled = false;

                btnThem.Enabled = true;
                btnSua.Enabled = true;
                btnXoa.Enabled = true;
                btnTim.Enabled = true;
                dgvSV.Enabled = true;
            }
        }

        private void txtHo_KeyPress(object sender, KeyPressEventArgs e)
        {
            e.Handled = ValidateInput(e);
        }

        private void txtTen_KeyPress(object sender, KeyPressEventArgs e)
        {
            e.Handled = ValidateInput(e);
        }

        private void btnCancel_Click(object sender, EventArgs e)
        {
            pnButtonThemHuy.Visible = false;
            dgvSV.Enabled = true;

            DataGridViewRow row = dgvSV.SelectedRows[0];
            txtMaSV.Text = row.Cells[0].Value.ToString();
            txtHo.Text = row.Cells[1].Value.ToString();
            txtTen.Text = row.Cells[2].Value.ToString();
            txtDiaChi.Text = row.Cells[4].Value.ToString();
            String sdate = row.Cells[3].Value.ToString();
            dtpNgaySinh.Value = new DateTime(int.Parse(sdate.Substring(6, 4)), int.Parse(sdate.Substring(3, 2)), int.Parse(sdate.Substring(0, 2)));

            txtMaSV.ReadOnly = true;
            txtHo.ReadOnly = true;
            txtTen.ReadOnly = true;
            txtDiaChi.ReadOnly = true;
            dtpNgaySinh.Enabled = false;

            btnThem.Enabled = true;
            btnXoa.Enabled = true;
            btnTim.Enabled = true;
            btnSua.Enabled = true;
        }

        private void btnXoa_Click(object sender, EventArgs e)
        {
            if (dgvSV.SelectedRows.Count > 0)
            {
                DataGridViewRow row = dgvSV.SelectedRows[0];

                DialogResult result = MessageBox.Show("Bạn chắc chắn muốn xóa thông tin sinh viên này?\nLưu ý: Bạn cũng đồng thời xóa bảng điểm (nếu có) của sinh viên này.", "Xác nhận", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
                if (result == DialogResult.Yes)
                {
                    SinhVien sv = new SinhVien(3, txtMaSV.Text, txtHo.Text, txtTen.Text, dtpNgaySinh.Value, txtDiaChi.Text, txtMaLop.Text);
                    undoStack.Push(sv);
                    btnUndo.Enabled = true;
                    dgvSV.Rows.RemoveAt(row.Index);
                }
            }
        }

        private void btnUndo_Click(object sender, EventArgs e)
        {
            if (undoStack.Count > 0)
            {
                SinhVien sv = undoStack.Peek();
                switch (sv.MaLenh)
                {
                    case 1:
                        foreach (DataGridViewRow r in dgvSV.Rows)
                        {
                            if (r.Cells[0].Value.ToString().Trim().Equals(sv.MaSV))
                            {
                                int index = r.Index;
                                if (index - 1 >= 0)
                                {
                                    dgvSV.Rows[index - 1].Selected = true;
                                }

                                dgvSV.Rows.RemoveAt(index);
                                undoStack.Pop();
                                if (undoStack.Count == 0)
                                    btnUndo.Enabled = false;
                                break;
                            }
                        }
                        break;
                    case 2:
                        foreach (DataGridViewRow r in dgvSV.Rows)
                        {
                            if (r.Cells[0].Value.ToString().Trim().Equals(sv.MaSV))
                            {
                                int index = r.Index;
                                r.Cells[0].Value = sv.MaSVOld;
                                r.Cells[1].Value = sv.HoOld;
                                r.Cells[2].Value = sv.TenOld;
                                DateTime date = sv.NgaySinhOld;
                                r.Cells[3].Value = date.Day.ToString("00") + "/" + date.Month.ToString("00") + "/" + date.Year;
                                r.Cells[4].Value = sv.DiaChiOld;

                                txtMaSV.Text = sv.MaSVOld;
                                txtHo.Text = sv.HoOld;
                                txtTen.Text = sv.TenOld;
                                dtpNgaySinh.Value = sv.NgaySinhOld;
                                txtDiaChi.Text = sv.DiaChiOld;
                                undoStack.Pop();
                                if (undoStack.Count == 0)
                                    btnUndo.Enabled = false;

                                dgvSV.Rows[index].Selected = true;
                                break;
                            }
                        }
                        break;
                    case 3:
                        DataGridViewRow row = new DataGridViewRow();
                        row.CreateCells(dgvSV);
                        row.Cells[0].Value = sv.MaSV;
                        row.Cells[1].Value = sv.Ho;
                        row.Cells[2].Value = sv.Ten;
                        row.Cells[4].Value = sv.DiaChi;
                        DateTime d = sv.NgaySinh;
                        row.Cells[3].Value = d.Day.ToString("00") + "/" + d.Month.ToString("00") + "/" + d.Year;

                        dgvSV.Rows.Add(row);
                        row.Selected = true;
                        dgvSV.Sort(dgvSV.Columns[0], ListSortDirection.Ascending);

                        undoStack.Pop();
                        if (undoStack.Count == 0)
                            btnUndo.Enabled = false;
                        break;
                }
            }
        }

        private void btnHuyAll_Click(object sender, EventArgs e)
        {
            this.Dispose();
        }

        private void btnGhiAll_Click(object sender, EventArgs e)
        {
            using (SqlConnection con = new SqlConnection(Program.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandType = CommandType.StoredProcedure;
                con.Open();
                for (int i = undoStack.Count - 1; i >= 0; i--)
                {
                    SinhVien obj = undoStack.ElementAt(i);
                    cmd.Parameters.Clear();
                    try
                    {
                        switch (obj.MaLenh)
                        {
                            case 1:
                                cmd.CommandText = "SP_INS_SV";
                                cmd.Parameters.Add("@MASV", SqlDbType.Char).Value = obj.MaSV;
                                cmd.Parameters.Add("@HO", SqlDbType.NVarChar).Value = obj.Ho;
                                cmd.Parameters.Add("@TEN", SqlDbType.NVarChar).Value = obj.Ten;
                                cmd.Parameters.Add("@NGAYSINH", SqlDbType.DateTime).Value = obj.NgaySinh;
                                cmd.Parameters.Add("@DIACHI", SqlDbType.NVarChar).Value = obj.DiaChi;
                                cmd.Parameters.Add("@MALOP", SqlDbType.Char).Value = obj.MaLop;
                                cmd.ExecuteNonQuery();
                                break;

                            case 2:
                                cmd.CommandText = "SP_UPDATE_SV";
                                cmd.Parameters.Add("@MACU", SqlDbType.Char).Value = obj.MaSVOld;
                                cmd.Parameters.Add("@MASV", SqlDbType.Char).Value = obj.MaSV;
                                cmd.Parameters.Add("@HO", SqlDbType.NVarChar).Value = obj.Ho;
                                cmd.Parameters.Add("@TEN", SqlDbType.NVarChar).Value = obj.Ten;
                                cmd.Parameters.Add("@NGAYSINH", SqlDbType.DateTime).Value = obj.NgaySinh;
                                cmd.Parameters.Add("@DIACHI", SqlDbType.NVarChar).Value = obj.DiaChi;
                                cmd.Parameters.Add("@MALOP", SqlDbType.Char).Value = DBNull.Value;
                                cmd.ExecuteNonQuery();
                                break;
                            case 3:
                                cmd.CommandText = "SP_DEL_SV";
                                cmd.Parameters.Add("@MASV", SqlDbType.Char).Value = obj.MaSV;
                                cmd.ExecuteNonQuery();
                                break;
                        }
                    }
                    catch (System.Exception ex)
                    {
                        MessageBox.Show(ex.Message);
                    }
                }
                undoStack.Clear();
                btnUndo.Enabled = false;
                MessageBox.Show("Ghi xuống cơ sở dữ liệu thành công!", "Xin chúc mừng", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }

        }

        private void btnQLDSLop_Click(object sender, EventArgs e)
        {
            FormQLDSLop frmLop = new FormQLDSLop();
            frmLop.Owner = this.Owner;
            frmLop.WindowState = FormWindowState.Normal;
            frmLop.Show();
        }

        private void txtMaSV_KeyPress(object sender, KeyPressEventArgs e)
        {
            e.Handled = ValidateInputMa(e);
        }
    }
}
